/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Helper.CategoryHelper;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author KIENLT
 */
public class CategoryDAO {
    public static int Insert(Connection cnn, CategoryHelper obj) throws Exception{
        String strSQL = "INSERT INTO tblCategories(CatName, SID, Description) VALUES(?,?,?)";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getName());
        stmt.setString(2, obj.getSupplierId());
        stmt.setString(3, obj.getDescription());
        return stmt.executeUpdate();
    }
    
    public static int Update(Connection cnn, CategoryHelper obj) throws Exception{
        String strSQL = "UPDATE tblCategories SET CatName=?, SID=?, Description=? WHERE CatID=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getName());
        stmt.setString(2, obj.getSupplierId());
        stmt.setString(3, obj.getDescription());
        stmt.setString(4, obj.getId());
        return stmt.executeUpdate();
    }
    
    public static int Delete(Connection cnn, CategoryHelper obj) throws Exception{
        String strSQL = "DELETE FROM tblCategories WHERE CatID = ?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        return stmt.executeUpdate();
    }
    
   public static ResultSet FindOne(Connection cnn, CategoryHelper obj) throws Exception {
        String strSQL = "SELECT * FROM tblCategories t1, tblSuppliers t2 WHERE t1.SID=t2.SID and CatID=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        return stmt.executeQuery();
    }
    
    public static ArrayList<CategoryHelper> FindAll(Connection cnn) throws Exception{
        ArrayList<CategoryHelper> arrCategoryList = new ArrayList<CategoryHelper>();
        CategoryHelper categoryData = null;
        String strSQL = "SELECT * FROM tblCategories t1, tblSuppliers t2 where t1.SID=t2.SID";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            categoryData = new CategoryHelper();
            categoryData.setId(rs.getString("CatID"));
            categoryData.setName(rs.getString("CatName"));
            categoryData.setSupplierName(rs.getString("SName"));
            categoryData.setDescription(rs.getString("Description"));
            arrCategoryList.add(categoryData);
        }
        return arrCategoryList;
    }
    
    public static ArrayList<CategoryHelper> Search(Connection cnn, CategoryHelper obj) throws Exception{
        ArrayList<CategoryHelper> arrCategoryList = new ArrayList<CategoryHelper>();
        String strSQL = "SELECT * FROM tblCategories t1, tblSuppliers t2 where t1.SID=t2.SID";
        if(!obj.getName().equals("")){
            strSQL += " and t1.CatName like '%"+obj.getName()+"%'";
        }
        if(!obj.getSupplierId().equals("")){
            strSQL += " and t1.SID = '"+obj.getSupplierId()+"'";
        }
        
        Statement stmt = cnn.createStatement();
        ResultSet rs = stmt.executeQuery(strSQL);
        while(rs.next()){
            obj = new CategoryHelper();
            obj.setId(rs.getString("CatID"));
            obj.setName(rs.getString("CatName"));
            obj.setSupplierName(rs.getString("SName"));
            obj.setDescription(rs.getString("Description"));
            arrCategoryList.add(obj);
        }
        return arrCategoryList;
    }
    
    public static ResultSet FillAll(Connection cnn, CategoryHelper obj) throws Exception {
        String strSQL = "SELECT * FROM tblCategories t1, tblSuppliers t2 WHERE t1.SID=t2.SID and t1.SID=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getSupplierId());
        return stmt.executeQuery();
    }
    
    public static ResultSet FillAll(Connection cnn) throws Exception {
        String strSQL = "SELECT * FROM tblCategories t1, tblSuppliers t2 WHERE t1.SID=t2.SID";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        return stmt.executeQuery();
    }
}
